﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ManagementBooks
{
    public partial class BackupAndRestore : Form
    {
        public BackupAndRestore()
        {
            InitializeComponent();
        }

        private void BackupAndRestore_Load(object sender, EventArgs e)
        {
            DataTable dt = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers();
            this.dataGridViewX1.DataSource = dt;
            string A = dataGridViewX1.Rows[0].Cells[0].Value.ToString();
            cbbserver.Items.Add(A);
            cbbserver.Items.Add(A + @"\SQLEXPRESS");
            cbbserver.SelectedIndex = 0;
            btNgatketnoi.Enabled = false;
            cbbCSDL.Enabled = false;
            btBackup.Enabled = false;
            btRestore.Enabled = false;
        }
        private SqlConnection conn;
        private SqlCommand command;
        private SqlDataReader reader;
        string sql = "";
        string connectionString = "";
        private void btKetnoi_Click(object sender, EventArgs e)
        {
            try
            {
                connectionString = "Data Source = " + cbbserver.Text + "; User Id = " + txtTendn.Text + "; Password = " + txtPass.Text + ";";
                conn = new SqlConnection(connectionString);
                conn.Open();
                sql = "SELECT * FROM sys.databases d WHERE d.database_id > 4";
                command = new SqlCommand(sql, conn);
                reader = command.ExecuteReader();
                cbbCSDL.Items.Clear();
                while (reader.Read())
                {
                    cbbCSDL.Items.Add(reader[0].ToString());
                }
                reader.Dispose();
                conn.Close();
                conn.Dispose();
                cbbserver.Enabled = false;
                txtPass.Enabled = false;
                txtTendn.Enabled = false;
                btBackup.Enabled = true;
                cbbCSDL.Enabled = true;
                btKetnoi.Enabled = false;
                btNgatketnoi.Enabled = true;
                btRestore.Enabled = true;
            }

            catch (Exception ex)
            {
                MessageBox.Show("Kết nối thất bại!", ex.Message);
            }
        }

        private void btNgatketnoi_Click(object sender, EventArgs e)
        {
            cbbserver.Enabled = true;
            txtTendn.Enabled = true;
            txtPass.Enabled = true;
            cbbCSDL.Enabled = false;
            btRestore.Enabled = false;
            btBackup.Enabled = false;
        }

        private void btDuongdanBackup_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dlg = new FolderBrowserDialog();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                txtDiachiBackup.Text = dlg.SelectedPath;
            }
        }

        private void btBackup_Click(object sender, EventArgs e)
        {
            if (cbbCSDL.Text.CompareTo("") == 0)
            {
                MessageBox.Show("Hãy lựa chọn cơ sở dữ liệu!");
                return;
            }
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                string sql = "BACKUP DATABASE " + cbbCSDL.Text + " TO DISK = '" + txtDiachiBackup.Text + "\\" + cbbCSDL.Text + "-" + DateTime.Now.Ticks.ToString() + ".bak'";
                command = new SqlCommand(sql, conn);
                command.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
                txtDiachiBackup.Text = "";
                MessageBox.Show("Backup cơ sở dữ liệu thành công!");
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Btduongdan_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Backup Files(*.bak)|*.bak|All Files(*.*)|*.*";
            dlg.FilterIndex = 0;
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                txtDiachirestore.Text = dlg.FileName;
            } 
        }

        private void btRestore_Click(object sender, EventArgs e)
        {
            try
            {
                if (cbbCSDL.Text.CompareTo("") == 0)
                {
                    MessageBox.Show("Hãy lựa chọn cơ sở dữ liệu.");
                    return;
                }
                conn = new SqlConnection(connectionString);
                conn.Open();
                sql = "USE MASTER ALTER DATABASE " + cbbCSDL.Text + "  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
                sql += "RESTORE DATABASE " + cbbCSDL.Text + "  FROM DISK ='" + txtDiachirestore.Text + "'  WITH REPLACE;";
                //sql += "ALTER DATABASE QuanLyBanMT SET MULTI_USER with ROLLBACK IMMEDIATE;";
                command = new SqlCommand(sql, conn);
                command.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
                MessageBox.Show("Khôi phục csdl thành công");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btThoat_Click(object sender, EventArgs e)
        {
            if (DialogResult.Yes == MessageBox.Show("Bạn có chắc muốn thoát hay không?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question))

                this.Close();
        }
    }
}
